﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Collections;
using System.Windows.Threading;

namespace AlgoLogicMessenger
{
    //Interaction logic for Window1.xaml
    public partial class ScoreBoard : Window
    {
        public ScoreBoard()
        {
            InitializeComponent();
            showColumnChart();
            DispatcherTimer timer = new DispatcherTimer();

            timer.Interval = TimeSpan.FromSeconds(30);
            timer.Tick += new EventHandler(delegate(object s, EventArgs ev)
            {
                valueList.Clear();
                barChart.DataContext = null;
                showColumnChart();
            });
            timer.Start();

        }
        string teamName = null;
        int score = 0;

        ArrayList scoreList = new ArrayList();
        List<KeyValuePair<string, int>> valueList = new List<KeyValuePair<string, int>>();

        // compare the value in the list
        static int Compare(KeyValuePair<string, int> a, KeyValuePair<string, int> b)
        {
            return a.Value.CompareTo(b.Value);
        }

        DateTime date = DateTime.Today;
        private void showColumnChart()
        {
            //string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDatabase;Persist Security Info=True;User ID = sa; Password = imsa";
            string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDB;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            //da.SelectCommand = new SqlCommand("SELECT distinct TeamName, SUM(Score) AS score FROM Result GROUP BY TeamName", conn);
            da.SelectCommand = new SqlCommand("SELECT distinct r.TeamName, SUM(r.Score)AS score FROM (SELECT Result.QuizNum,Result.TeamName,Result.Score,CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS DATETIME) AS dt FROM dbo.Result," +
                                  "(SELECT QuizNum,MIN(CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS DATETIME)) AS fastest FROM dbo.Result WHERE Score = 1 AND Date=@date GROUP BY QuizNum) AS t WHERE Result.QuizNum = t.QuizNum" +
                                  " AND CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS DATETIME) = t.fastest) AS r GROUP BY r.TeamName", conn);
            da.SelectCommand.Parameters.AddWithValue("@date", date);
            SqlDataReader dr = da.SelectCommand.ExecuteReader();

            ArrayList teamming = new ArrayList();
            while (dr.Read())
            {
                teamName = dr["TeamName"].ToString();
                score = Convert.ToInt32(dr["score"]);

                valueList.Add(new KeyValuePair<string, int>(teamName, score));
                teamming.Add(teamName);

            }
            conn.Close();

            SqlConnection conn2 = new SqlConnection(connstr);
            conn2.Open();
            SqlDataAdapter da2 = new SqlDataAdapter();
            //da2.SelectCommand = new SqlCommand("SELECT DISTINCT r.TeamName, r.Score FROM(SELECT TeamName,Score From dbo.Result WHERE Score = 0 AND Date=@date) AS r GROUP BY r.TeamName, r.Score", conn2);
            da2.SelectCommand = new SqlCommand("SELECT TeamName, Score FROM Result WHERE Score = 1 AND Date=@date", conn2);
            da2.SelectCommand.Parameters.AddWithValue("@date", date);
            SqlDataReader dr2 = da2.SelectCommand.ExecuteReader();
            while (dr2.Read())
            {
                teamName = dr2["TeamName"].ToString();
                score = Convert.ToInt32(dr2["Score"]);

                if (!teamming.Contains(teamName))
                {
                    score = 0;
                    valueList.Add(new KeyValuePair<string, int>(teamName, score));
                }
            }
            //END HERE
            conn2.Close();

            //CODE CHANGE(COPY FROM HERE)
            SqlConnection conn1 = new SqlConnection(connstr);
            conn1.Open();
            SqlDataAdapter da1 = new SqlDataAdapter();
            da1.SelectCommand = new SqlCommand("SELECT DISTINCT r.TeamName, r.Score FROM(SELECT TeamName,Score From dbo.Result WHERE Score = 0 AND Date=@date) AS r GROUP BY r.TeamName, r.Score", conn1);
            da1.SelectCommand.Parameters.AddWithValue("@date", date);
            SqlDataReader dr1 = da1.SelectCommand.ExecuteReader();
            while (dr1.Read())
            {
                teamName = dr1["TeamName"].ToString();
                score = Convert.ToInt32(dr1["Score"]);

                if (!teamming.Contains(teamName))
                {
                    valueList.Add(new KeyValuePair<string, int>(teamName, score));
                }
            }
            //END HERE
            conn1.Close();

            valueList.Sort(Compare);
            barChart.DataContext = valueList;
        }

        private void Window_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.F5 || e.Key == Key.Back)
            {
                e.Handled = true;
            }
        }
    }
}